/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package projbd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Matheus
 */
public class DataBaseOperations {

    private static final String sw = "'";
    
    private Connection connection;
    private Statement statement;
    
    DataBaseOperations() throws SQLException {
        connection = DriverManager.getConnection(
               "jdbc:postgresql://localhost:5432/shakespeare", "postgres", "admin");  
        statement = connection.createStatement();
    }
    
    public void update(String id, String value, String attributes, String dataType, boolean abs) throws SQLException {
        String idPath = "1";
        String update = new String();      
        
        if (abs) {
            update = "INSERT INTO dewey_edges(dewey,path_id,value,attributes,datatype) " + sw + id + sw
                                                                                         + "," + sw + idPath + sw
                                                                                         + "," + sw + value + sw
                                                                                         + "," + sw + attributes + sw
                                                                                         + "," + sw + dataType + ")";
        } else {
            String findFather = "SELECT max(dewey) from dewey_edges WHERE father_id = " + sw + id + sw;
            ResultSet result = statement.executeQuery(findFather);
            String lastChild = result.toString();
            
            int i;
            for (i = 1; (i < lastChild.length()); i++);
            while (lastChild.charAt(i) != '.') i--;
            String lastBrother = lastChild.substring((i+1), (lastChild.length()-1));
            Long lb = Long.parseLong(lastBrother);
            lb++;
            String newChild = lb.toString();
            id = id + "." + newChild;

            update = "INSERT INTO dewey_edges(dewey,path_id,value,attributes,datatype) " + sw + id + sw
                                                                                         + "," + sw + idPath + sw
                                                                                         + "," + sw + value + sw
                                                                                         + "," + sw + attributes + sw
                                                                                         + "," + sw + dataType + ")";
        }   
        
        try {
            statement.executeUpdate(update);
        } catch (SQLException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(update);
        }
    }

    public void delete(String id) {
        String delete = "DELETE CASCADE FROM dewey_edges WHERE dewey = " + sw + id + sw + ")";
        try {
            statement.executeUpdate(delete);
        } catch (SQLException ex) {
            Logger.getLogger(XMLParser.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(delete);            
        }
    }
    
}
